﻿using System;
using System.Collections.Generic;
using System.Text;
using LibraryONTrack.dto;
using System.Data.SQLite;

namespace LibraryONTrack.dao
{
	public class OverdueBookDAO : BaseDAO, IBaseDAO<OverdueBook>
	{

		public static DateTime ConvertFromUnixTimestamp(double timestamp)
		{
			DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
			return origin.AddSeconds(timestamp);
		}


		public static double ConvertToUnixTimestamp(DateTime date)
		{
			DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
			TimeSpan diff = date - origin;
			return Math.Floor(diff.TotalSeconds);
		}

		public OverdueBook GetBorrowerOverdueBook(long borrowerId, long bookId)
		{
			string query =
				@"SELECT
						ob.book_id, b.title, ob.notice_count, ob.still_overdue_ind, ob.last_notice_sent_unixtime, 
            ob.borrower_id, snr.name as borrower_name, tr.teacher_id, tnr.name as teacher_name
					FROM overdue_book ob
						INNER JOIN book b
							ON b.book_id = ob.book_id
						INNER JOIN roster snr
						  ON snr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tr
							ON tr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tnr
							ON tnr.student_teacher_id = tr.teacher_id
					WHERE ob.borrower_id = @borrower_id AND ob.book_id = @book_id;
				";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@borrower_id", borrowerId));
			command.Parameters.Add(dao.CreateParameter("@book_id", bookId));
			return dao.GetSingle<OverdueBook>(command);
		}

		public List<OverdueBook> GetBorrowerOverdueBooks(long borrowerId)
		{
			string query =
				@"SELECT
						ob.book_id, b.title, ob.notice_count, ob.still_overdue_ind, ob.last_notice_sent_unixtime, 
            ob.borrower_id, snr.name as borrower_name, tr.teacher_id, tnr.name as teacher_name
					FROM overdue_book ob
						INNER JOIN book b
							ON b.book_id = ob.book_id
						INNER JOIN roster snr
						  ON snr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tr
							ON tr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tnr
							ON tnr.student_teacher_id = tr.teacher_id
					WHERE ob.borrower_id = @borrower_id
					ORDER BY b.title;
				";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@borrower_id", borrowerId));
			return dao.GetList<OverdueBook>(command);
		}

		public List<OverdueBook> GetAllOverdueBooks()
		{
			string query =
				@"SELECT
						ob.book_id, b.title, ob.notice_count, ob.still_overdue_ind, ob.last_notice_sent_unixtime, 
            ob.borrower_id, snr.name as borrower_name, tr.teacher_id, tnr.name as teacher_name
					FROM overdue_book ob
						INNER JOIN book b
							ON b.book_id = ob.book_id
						INNER JOIN roster snr
						  ON snr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tr
							ON tr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tnr
							ON tnr.student_teacher_id = tr.teacher_id
					ORDER BY tnr.name, snr.name, b.title;					
				";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			return dao.GetList<OverdueBook>(command);
		}

		public OverdueBook GetOverdueBook(long bookId)
		{
			string query =
				@"SELECT
						ob.book_id, b.title, ob.notice_count, ob.still_overdue_ind, ob.last_notice_sent_unixtime, 
            ob.borrower_id, snr.name as borrower_name, tr.teacher_id, tnr.name as teacher_name
					FROM overdue_book ob
						INNER JOIN book b
							ON b.book_id = ob.book_id
						INNER JOIN roster snr
						  ON snr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tr
							ON tr.student_teacher_id = ob.borrower_id
						INNER JOIN roster tnr
							ON tnr.student_teacher_id = tr.teacher_id
					WHERE ob.book_id = @book_id;
				";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@book_id", bookId));
			return dao.GetSingle<OverdueBook>(command);
		}						


		public List<OverdueBook> GetAll()
		{
			string query = @"SELECT
											   book_id, borrower_id, notice_count
                       FROM overdue_book;";
			SqlDao dao = SqlDao;
			return dao.GetList<OverdueBook>(dao.GetSQLiteCommand(query));
		}

		public List<OverdueBook> GetAll2()
		{
			string query = @"SELECT
												a.book_id, a.title, 
											   book_id, borrower_id, notice_count
                       FROM overdue_book;";
			SqlDao dao = SqlDao;
			return dao.GetList<OverdueBook>(dao.GetSQLiteCommand(query));
		}


		public OverdueBook GetById(long id)
		{
			throw new NotImplementedException();
		}

		public List<OverdueBook> GetByBorrowerId(long borrower_id)
		{
			string query = @"SELECT
											   book_id, borrower_id, notice_count
                       FROM overdue_book
											 WHERE borrower_id = @borrower_id;";

			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@borrower_id", borrower_id));
			return dao.GetList<OverdueBook>(command);
		}

		public OverdueBook GetById(long book_id, long borrower_id)
		{
			string query = @"SELECT
											   book_id, borrower_id, notice_count
                       FROM overdue_book
											 WHERE book_id = @book_id and borrower_id = @borrower_id;";

			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@book_id", book_id));
			command.Parameters.Add(dao.CreateParameter("@borrower_id", borrower_id));
			return dao.GetSingle<OverdueBook>(command);
		}

		public Int32 GetOverdueNoticeCount(int notice_count)
		{
			string @query = @"SELECT COUNT(*) 
												FROM overdue_book 
												WHERE notice_count=@notice_count;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@notice_count", notice_count));
			return dao.GetSingleInt32(command);
		}


		public OverdueBook Insert(OverdueBook t)
		{
			SqlDao dao = SqlDao;
			string sql = 
				@"INSERT INTO 
						[overdue_book]
						(book_id, borrower_id, notice_count, still_overdue_ind, last_notice_sent_unixtime)
					VALUES
						(@book_id, @borrower_id, @notice_count, 1, null);";

			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@book_id", t.Book.BookId));
			command.Parameters.Add(dao.CreateParameter("@borrower_id", t.Borrower.Id));
			command.Parameters.Add(dao.CreateParameter("@notice_count", t.NoticeCount));
			dao.ExecuteNonQuery(command);
			return t;
		}

		public OverdueBook Update(OverdueBook t)
		{
			SqlDao dao = SqlDao;
			string sql =
				@"UPDATE 
						[overdue_book]
					SET notice_count=@notice_count, 
							still_overdue_ind=@still_overdue_ind,
							last_notice_sent_unixtime=@last_notice_sent_unixtime
					WHERE book_id=@book_id AND borrower_id=@borrower_id;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@book_id", t.Book.BookId));
			command.Parameters.Add(dao.CreateParameter("@borrower_id", t.Borrower.Id));
			command.Parameters.Add(dao.CreateParameter("@notice_count", t.NoticeCount));
			command.Parameters.Add(dao.CreateParameter("@still_overdue_ind", t.StillOverdue));
			command.Parameters.Add(dao.CreateParameter("@last_notice_sent_unixtime", (int)ConvertToUnixTimestamp(t.LastNoticeSentDateTime)));
			dao.ExecuteNonQuery(command);
			return t;
		}

		public void Delete(long id)
		{
			SqlDao dao = SqlDao;
			string sql = @"delete from [overdue_book]
										 where borrower_id=@borrower_id;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@borrower_id", id));
			dao.ExecuteNonQuery(command);
		}


		public void Delete(long book_id, long borrower_id)
		{
			SqlDao dao = SqlDao;
			string sql = @"delete from [overdue_book]
										 where book_id=@book_id and borrower_id=@borrower_id;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@book_id", book_id));
			command.Parameters.Add(dao.CreateParameter("@borrower_id", borrower_id));
			dao.ExecuteNonQuery(command);
		}

		public void Delete(OverdueBook t)
		{
//			this.Delete(t.BookId, t.BorrowerId);
		}

		public void Truncate()
		{
			SqlDao dao = new SqlDao();
			string sql = @"delete from overdue_book;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			dao.ExecuteNonQuery(command);
		}
	}
}
